Adapting a relational query to accommodate hierarchical data

ABSTRACT

A method, system and computer program product for processing hierarchical data is provided. A method for processing hierarchical data in a relational database includes receiving an input parameter in an initial query for querying an array in a hierarchical data object stored in a table of a relational database, and transforming the initial query into a transformed query configured to place all elements of the array into a different table and to apply a sub-query against the table for the input parameter. Once transformed, predicate evaluation logic of a relational engine of the relational database is applied to the transformed query that is then mapped to the initial query. Finally, responsive to receiving a subsequent instance of the initial query, the mapping is referred to in order to replace the initial query with the transformed query and to subsequently apply the predicate evaluation logic to the transformed query.

BACKGROUND OF THE INVENTION Field of the Invention

The present invention relates to hierarchical data representation in acomputer program and more particularly to processing hierarchical dataprocessing in a relational database management system.

Description of the Related Art

Hierarchical data refers to a data structure defining data objects of aone-to-many relationship in which a parent object can have one or morechildren, but each child can have only one parent. Commonly envisionedas a tree, hierarchical data remains an important tool in modeling realworld object relationships. Relational data, in the other hand, is bestknown as the basis for the relational model of database management inwhich all data is represented in terms of tuples, and grouped intorelations so as to permit access according to first-order predicatelogic. A database organized in terms of the relational model is arelational database which, in most cases, use the structured querylanguage (SQL) data definition and query language. To that end, a tablein an SQL database schema corresponds to a predicate variable; thecontents of a table to a relation; key constraints, other constraints,and SQL queries correspond to predicates.

The Javascript Object Notation (JSON) is a form of hierarchical datarepresentation. JSON is built upon a collection of name/value pairs andan ordered list of values. In JSON, a hierarchical data object includesan unordered set of name/value pairs and is denoted with a beginningleft brace and an ending right brace. Each name in a JSON object isfollowed by a colon and the name/value pairs are separated by a comma.Of note, a JSON object can include one or more of the name/value pairssuch that the name/value pairs include but a single value. Yet, a JSONobject also can include a name/value pair that includes an array ofvalues for a single name.

Of note, JSON has been viewed as a universal data representation able tobe integrated in a multiplicity of programming languages. Yet,integrating JSON within a relational database is not without itschallenges. Specifically, while a scalar value can be retrieved from aJSON object utilizing the “JSON VAL” function by reference to a namecorresponding to the value, retrieving a value amongst a set of valuesin an array in a JSON is more complicated. One possible solution is topre-specify which element of an array is desirable, but to do so wouldrequire a priori a number of elements in the array. Another possiblesolution is to utilize a SQL table function that can return each arrayelement as a separate row to the relational database. Both solutions,however, cannot be indexed and therefore are not satisfactory.

The problem of processing array values in a JSON document can becomecompounded in attempting to process hierarchical relationships. In thisinstance, a function call first is required to return a set of recordscontaining the values for the hierarchical relationship. Thereafter, anindex must be built on the result set and sorted to provide readyidentification of the hierarchical relationship. In doing so, however,it can be imperative to ensure type conformity so as to handle anexception during the sorting portion of the process.

BRIEF SUMMARY OF THE INVENTION

Embodiments of the present invention address deficiencies of the art inrespect to relational database management and provide a novel andnon-obvious method, system and computer program product for processinghierarchical data in a relational database. In one embodiment of theinvention, a method for processing hierarchical data, such as data in aJSON object, in a relational database includes receiving an inputparameter in an initial SQL query for querying an array in ahierarchical data object stored in a table of a relational database, andtransforming the initial SQL query into a transformed SQL queryconfigured to place all elements of the array into a different table andto apply a sub-query against the table for the input parameter. Oncetransformed, predicate evaluation logic of a relational engine of therelational database is applied to the transformed SQL query and thetransformed SQL query is then mapped to the initial SQL query. Finally,responsive to receiving a subsequent instance of the initial SQL query,the mapping can be referred to in order to replace the initial SQL querywith the transformed SQL query and to subsequently apply the predicateevaluation logic of the relational engine to the transformed SQL query.

In one aspect of the embodiment, the elements of the array areexplicitly typecast to a specified data type, for instance an integerdata type, and in response to detecting a failure of the typecasting, aNULL value is returned to the relational engine. As well, a charactersize of the elements of the array can be limited to a specified width.Thereafter, in response to detecting an element of the array having acharacter size that exceeds the specified width, a NULL value isreturned to the relational engine. In this way, the hierarchical datawithin a JSON object can be handled in the relational engine despite thepossibility that the value of the array of the JSON object is of anunexpected data type or exceeds a certain character width.

In another embodiment of the invention, a relational database dataprocessing system is provided. The system includes a host computingsystem comprising at least one computer with memory and at least oneprocessor. The system also includes fixed storage disposed in the hostcomputing system and a relational database stored in the fixed storage,the relational database storing a different hierarchical data objects.The system yet further includes a relational engine executing in thememory of the host computing system and managing queries upon thehierarchical data objects of the relational database. Finally, thesystem includes a hierarchical data processing module coupled to therelational engine.

The module includes program code enabled upon execution in the memory ofthe host computing system to receive an input parameter in an initialquery for querying an array in a hierarchical data object stored in atable of the relational database. The program code also is enabled totransform the initial query into a transformed query configured to placeall elements of the array into a different table and to apply asub-query against the table for the input parameter. The program codeyet further is enabled to direct the relational engine to applypredicate evaluation logic of the relational engine to the transformedquery, and to map the transformed query to the initial query. Finally,the program code is enabled to respond to receiving a subsequentinstance of the initial query, by referring to the mapping to replacethe initial query with the transformed query and by subsequentlydirecting the relational engine to apply the predicate evaluation logicto the transformed query.

Additional aspects of the invention will be set forth in part in thedescription which follows, and in part will be obvious from thedescription, or may be learned by practice of the invention. The aspectsof the invention will be realized and attained by means of the elementsand combinations particularly pointed out in the appended claims. It isto be understood that both the foregoing general description and thefollowing detailed description are exemplary and explanatory only andare not restrictive of the invention, as claimed.

BRIEF DESCRIPTION OF THE SEVERAL VIEWS OF THE DRAWINGS

The accompanying drawings, which are incorporated in and constitute partof this specification, illustrate embodiments of the invention andtogether with the description, serve to explain the principles of theinvention. The embodiments illustrated herein are presently preferred,it being understood, however, that the invention is not limited to theprecise arrangements and instrumentalities shown, wherein:

FIG. 1 is a pictorial illustration of a process for processinghierarchical data in a relational database;

FIG. 2 is a schematic illustration of a relational database dataprocessing system configured for processing hierarchical data in arelational database;

FIG. 3A is a flow chart illustrating a process for query transformationto accommodate hierarchical data in a relational database; and,

FIG. 3B is a flow chart illustrating a process for imposing datalimitations upon hierarchical data disposed within a relationaldatabase.

DETAILED DESCRIPTION OF THE INVENTION

Embodiments of the invention provide for processing hierarchical data ina relational database. In accordance with an embodiment of theinvention, an initial query can be received which specifies a request toextract an array element in an array from a hierarchical data objectdisposed in a table of a relational database. The initial query can betransformed into a query that extracts all elements of the array into adifferent table and the transformed query can be applied by therelational engine of the relational database. The transformed querythereafter can be mapped to the initial query such that a subsequentinvocation of the initial query can be replaced with the transformedquery and applied by the relational engine. In this way, optimal queryperformance can be achieved for all subsequent invocations of theinitial query.

In further illustration, FIG. 1 pictorial shows of a process forprocessing hierarchical data in a relational database. As shown in FIG.1, an end user 110 can issue an initial SQL query 120 to a relationaldatabase 130 in order to retrieve a particular array element of an arraywithin a hierarchical data object such as a JSON object disposed withinthe relational database 130. Hierarchical data processor 140 can receivethe initial SQL query 120 and the hierarchical data processor 140 cantransform the initial SQL query 120 into a transformed SQL query thatseeks to retrieve all of the elements of the array within thehierarchical data object, and then sub-select amongst all of theelements. Optionally, a typecast 160 can be applied to the elements soas to manage any type mismatches in the array. Further, a widthlimitation 170 can be applied to the elements so as to manage any datamismatches in the array.

Once the hierarchical data processor 140 has transformed the initial SQLquery 120 into the transformed SQL query 150, the hierarchical dataprocessor 140 can direct a relational engine managing access to therelational database 130 to apply the transformed SQL query 150 so as toproduce a result set 180 for presentation to the end user 110.Additionally, the hierarchical data processor 140 can store a mapping190 of the initial SQL query 120 to the transformed SQL query 150. Inthis way, subsequent invocations of the initial SQL query 120 can bereplaced with the transformed query 150 without requiring a newtransformation of the initial SQL query 120.

The process described in connection with FIG. 1 can be implemented in arelational database data processing system. In yet further illustration,FIG. 2 schematically shows a relational database data processing systemconfigured for processing hierarchical data in a relational database.The system includes a host computing system 210 that includes one ormore computing servers, each with memory and at least one processor. Thehost computing system 210 can support the operation of a relationaldatabase management system 220 managing access to a relational database240 by way of a relational engine 230 configured to process SQL queriesreceived from one or more query clients 260 executing in respectivelydifferent client computers 250 from over computer communications network270, against the data in one or more tables of the relational database240.

Notably, a hierarchical data processing module 300 can execute in thememory of the host computing system 210. The hierarchical dataprocessing module 300 can include program code that when executed in thememory of the host computing system 210 is enabled to process an initialSQL query received in the relational engine 230, the initial SQL queryseeking an element of an array of a JSON object. The processingperformed by the program code transforms the initial SQL query into atransformed SQL query seeking to place all elements of the array of theJSON object into a table and the sub-selection of the desired elementfrom the table. The processing performed by the program code also mapsthe initial SQL query to the transformed SQL query in a query map 280 sothat subsequent instance of the initial SQL query can be immediatelyreplaced with the transformed SQL query. Finally, the processingperformed by the program code can direct the relational engine 230 toapply the transformed SQL query to the relational database 240.

In even yet further illustration of the operation of the hierarchicaldata processing module 300, FIG. 3A is a flow chart illustrating aprocess for query transformation to accommodate hierarchical data in arelational database. Beginning in block 310, an initial SQL query can bereceived to access an element of an array of a JSON object stored in therelational database. In decision block 320, it can be determined whetheror not a transformed SQL query has been previously mapped to the initialSQL query. If so, in block 330 the previously mapped transformed SQLquery can be retrieved and the relational engine can be directed toapply the transformed SQL query in block 360.

In decision block 320, however, if no previous mapping exists for theinitial SQL query, in block 340 the initial SQL query can be transformedinto a transformed SQL query by specifying a retrieval of all elementsof the array of the JSON object into a table and the subsequentselection of the desired element from amongst the entries in the table.Thereafter, in block 350 the initial SQL query can be mapped to thetransformed SQL query. Finally, the relational engine can be directed toapply the transformed SQL query in block 360.

Of note, so as to accommodate the hierarchical data of the JSON object,typecasting and value constraints can be applied to the elements of thearray of the JSON object. In even yet further illustration, FIG. 3B is aflow chart illustrating a process for imposing data limitations uponhierarchical data disposed within a relational database. Beginning inblock 370, an initial SQL query can be received specifying a singleelement of an array of a JSON object disposed within the relationaldatabase. In block 380, the initial SQL query can be transformed into atransformed SQL query that specifies the retrieval of all elements ofthe array of the JSON object into a table. In block 390, a value of atleast one field of the array can be typecast to a particular data type.Further, in block 400 a limitation of character width can be specifiedas a constraint to a value of at least one field of the array.Thereafter in block 410 the relational engine can be directed to applythe transformed query to the JSON object of the relational database.

The present invention may be embodied within a system, a method, acomputer program product or any combination thereof. The computerprogram product may include a computer readable storage medium or mediahaving computer readable program instructions thereon for causing aprocessor to carry out aspects of the present invention. The computerreadable storage medium can be a tangible device that can retain andstore instructions for use by an instruction execution device. Thecomputer readable storage medium may be, for example, but is not limitedto, an electronic storage device, a magnetic storage device, an opticalstorage device, an electromagnetic storage device, a semiconductorstorage device, or any suitable combination of the foregoing.

A non-exhaustive list of more specific examples of the computer readablestorage medium includes the following: a portable computer diskette, ahard disk, a random access memory (RAM), a read-only memory (ROM), anerasable programmable read-only memory (EPROM or Flash memory), a staticrandom access memory (SRAM), a portable compact disc read-only memory(CD-ROM), a digital versatile disk (DVD), a memory stick, a floppy disk,a mechanically encoded device such as punch-cards or raised structuresin a groove having instructions recorded thereon, and any suitablecombination of the foregoing. A computer readable storage medium, asused herein, is not to be construed as being transitory signals per se,such as radio waves or other freely propagating electromagnetic waves,electromagnetic waves propagating through a waveguide or othertransmission media (e.g., light pulses passing through a fiber-opticcable), or electrical signals transmitted through a wire.

Computer readable program instructions described herein can bedownloaded to respective computing/processing devices from a computerreadable storage medium or to an external computer or external storagedevice via a network, for example, the Internet, a local area network, awide area network and/or a wireless network. The network may comprisecopper transmission cables, optical transmission fibers, wirelesstransmission, routers, firewalls, switches, gateway computers and/oredge servers. A network adapter card or network interface in eachcomputing/processing device receives computer readable programinstructions from the network and forwards the computer readable programinstructions for storage in a computer readable storage medium withinthe respective computing/processing device.

Computer readable program instructions for carrying out operations ofthe present invention may be assembler instructions,instruction-set-architecture (ISA) instructions, machine instructions,machine dependent instructions, microcode, firmware instructions,state-setting data, or either source code or object code written in anycombination of one or more programming languages, including an objectoriented programming language such as Smalltalk, C++ or the like, andconventional procedural programming languages, such as the “C”programming language or similar programming languages. The computerreadable program instructions may execute entirely on the user'scomputer, partly on the user's computer, as a stand-alone softwarepackage, partly on the user's computer and partly on a remote computeror entirely on the remote computer or server. In the latter scenario,the remote computer may be connected to the user's computer through anytype of network, including a local area network (LAN) or a wide areanetwork (WAN), or the connection may be made to an external computer(for example, through the Internet using an Internet Service Provider).In some embodiments, electronic circuitry including, for example,programmable logic circuitry, field-programmable gate arrays (FPGA), orprogrammable logic arrays (PLA) may execute the computer readableprogram instructions by utilizing state information of the computerreadable program instructions to personalize the electronic circuitry,in order to perform aspects of the present invention.

Aspects of the present invention are described herein with reference toflowchart illustrations and/or block diagrams of methods, apparatus(systems), and computer program products according to embodiments of theinvention. It will be understood that each block of the flowchartillustrations and/or block diagrams, and combinations of blocks in theflowchart illustrations and/or block diagrams, can be implemented bycomputer readable program instructions.

These computer readable program instructions may be provided to aprocessor of a general purpose computer, special purpose computer, orother programmable data processing apparatus to produce a machine, suchthat the instructions, which execute via the processor of the computeror other programmable data processing apparatus, create means forimplementing the functions/acts specified in the flowchart and/or blockdiagram block or blocks. These computer readable program instructionsmay also be stored in a computer readable storage medium that can directa computer, a programmable data processing apparatus, and/or otherdevices to function in a particular manner, such that the computerreadable storage medium having instructions stored therein comprises anarticle of manufacture including instructions which implement aspects ofthe function/act specified in the flowchart and/or block diagram blockor blocks.

The computer readable program instructions may also be loaded onto acomputer, other programmable data processing apparatus, or other deviceto cause a series of operational steps to be performed on the computer,other programmable apparatus or other device to produce a computerimplemented process, such that the instructions which execute on thecomputer, other programmable apparatus, or other device implement thefunctions/acts specified in the flowchart and/or block diagram block orblocks.

The flowchart and block diagrams in the Figures illustrate thearchitecture, functionality, and operation of possible implementationsof systems, methods, and computer program products according to variousembodiments of the present invention. In this regard, each block in theflowchart or block diagrams may represent a module, segment, or portionof instructions, which comprises one or more executable instructions forimplementing the specified logical function(s). In some alternativeimplementations, the functions noted in the block may occur out of theorder noted in the figures. For example, two blocks shown in successionmay, in fact, be executed substantially concurrently, or the blocks maysometimes be executed in the reverse order, depending upon thefunctionality involved. It will also be noted that each block of theblock diagrams and/or flowchart illustration, and combinations of blocksin the block diagrams and/or flowchart illustration, can be implementedby special purpose hardware-based systems that perform the specifiedfunctions or acts or carry out combinations of special purpose hardwareand computer instructions.

Finally, the terminology used herein is for the purpose of describingparticular embodiments only and is not intended to be limiting of theinvention. As used herein, the singular forms “a”, “an” and “the” areintended to include the plural forms as well, unless the context clearlyindicates otherwise. It will be further understood that the terms“comprises” and/or “comprising,” when used in this specification,specify the presence of stated features, integers, steps, operations,elements, and/or components, but do not preclude the presence oraddition of one or more other features, integers, steps, operations,elements, components, and/or groups thereof.

The corresponding structures, materials, acts, and equivalents of allmeans or step plus function elements in the claims below are intended toinclude any structure, material, or act for performing the function incombination with other claimed elements as specifically claimed. Thedescription of the present invention has been presented for purposes ofillustration and description, but is not intended to be exhaustive orlimited to the invention in the form disclosed. Many modifications andvariations will be apparent to those of ordinary skill in the artwithout departing from the scope and spirit of the invention. Theembodiment was chosen and described in order to best explain theprinciples of the invention and the practical application, and to enableothers of ordinary skill in the art to understand the invention forvarious embodiments with various modifications as are suited to theparticular use contemplated.

Having thus described the invention of the present application in detailand by reference to embodiments thereof, it will be apparent thatmodifications and variations are possible without departing from thescope of the invention defined in the appended claims as follows:

1. A method for processing hierarchical data in a relational databasecomprising: receiving from an end user an input parameter in an initialquery for querying an array in a hierarchical data object stored in atable of a relational database, the initial query requesting anextraction of a specific array element in the array; transforming theinitial query into a transformed query configured to extract allelements of the array into a different table and to apply a sub-queryagainst the table for the input parameter; applying predicate evaluationlogic of a relational engine of the relational database to thetransformed query, the application producing a result set forpresentation to the end user; mapping the transformed query to theinitial query such that a subsequent invocation of the initial query isreplaced with the transformed query and applied by the relationalengine; and, responsive to receiving a subsequent instance of theinitial query, optimizing query performance for the subsequent instanceof the initial query by referring to the mapping to replace the initialquery with the transformed query and subsequently applying the predicateevaluation logic of the relational engine to the transformed querywithout requiring a new transformation of the initial query.
 2. Themethod of claim 1, wherein the hierarchical data object is a JavascriptObject Notation (JSON) object.
 3. The method of claim 1, wherein theinitial query and the transformed query are structured query language(SQL) queries.
 4. The method of claim 1, further comprising: explicitlytypecasting the elements of the array to a specified data type; and,responsive to detecting a failure of the typecasting, returning a NULLvalue to the relational engine.
 5. The method of claim 4, wherein thespecified data type is an integer data type.
 6. (canceled)
 7. Arelational database data processing system comprising: a host computingsystem comprising at least one computer with memory and at least oneprocessor; fixed storage disposed in the host computing system; arelational database stored in the fixed storage, the relational databasestoring a plurality of hierarchical data objects; a relational engineexecuting in the memory of the host computing system and managingqueries upon the hierarchical data objects of the relational database;and, a hierarchical data processing module coupled to the relationalengine, the module comprising program code enabled upon execution in thememory of the host computing system to receive from an end user an inputparameter in an initial query for querying an array in a hierarchicaldata object stored in a table of a relational database, the initialquery requesting an extraction of a specific array element in the array,to transform the initial query into a transformed query configured toextract all elements of the array into a different table and to apply asub-query against the table for the input parameter, to direct therelational engine to apply predicate evaluation logic of a relationalengine of the relational database to the transformed query, theapplication producing a result set for presentation to the end user, tomap the transformed query to the initial query such that a subsequentinvocation of the initial query is replaced with the transformed queryand applied by the relational engine, and to respond to receiving asubsequent instance of the initial query, by optimizing queryperformance for the subsequent instance of the initial query byreferring to the mapping to replace the initial query with thetransformed query and subsequently applying the predicate evaluationlogic of the relational engine to the transformed query withoutrequiring a new transformation of the initial query.
 8. The system ofclaim 7, wherein the hierarchical data object is a Javascript ObjectNotation (JSON) object.
 9. The system of claim 7, wherein the initialquery and the transformed query are structured query language (SQL)queries.
 10. The system of claim 7, wherein the program code of themodule is further enabled to explicitly typecast the elements of thearray to a specified data type and to respond to detecting a failure ofthe typecasting, by returning a NULL value to the relational engine. 11.The system of claim 10, wherein the specified data type is an integerdata type.
 12. (canceled)
 13. A computer program product for processinghierarchical data in a relational database, the computer program productcomprising a computer readable storage medium having programinstructions embodied therewith, the program instructions executable bya device to cause the device to perform a method comprising: receiving,by a device, from an end user an input parameter in an initial query forquerying an array in a hierarchical data object stored in a table of arelational database, the initial query requesting an extraction of aspecific array element in the array; transforming, by the device, theinitial query into a transformed query configured to extract allelements of the array into a different table and to apply a sub-queryagainst the table for the input parameter; applying, by the device,predicate evaluation logic of a relational engine of the relationaldatabase to the transformed query, the application producing a resultset for presentation to the end user; mapping, by the device, thetransformed query to the initial query such that a subsequent invocationof the initial query is replaced with the transformed query and appliedby the relational engine; and, responsive to receiving a subsequentinstance of the initial query, optimizing query performance for thesubsequent instance of the initial query by referring, by the device, tothe mapping to replace the initial query with the transformed query andsubsequently applying the predicate evaluation logic of the relationalengine to the transformed query without requiring a new transformationof the initial query.
 14. The computer program product of claim 13,wherein the hierarchical data object is a Javascript Object Notation(JSON) object.
 15. The computer program product of claim 13, wherein theinitial query and the transformed query are structured query language(SQL) queries.
 16. The computer program product of claim 13, furthercomprising: explicitly typecasting, by the device, the elements of thearray to a specified data type; and, responsive to detecting a failureof the typecasting, returning, by the device, a NULL value to therelational engine.
 17. The computer program product of claim 16, whereinthe specified data type is an integer data type.
 18. (canceled)